package com.qfedu.empproject.dao.impl;

import com.qfedu.empproject.dao.EmpDao;
import com.qfedu.empproject.entity.Emp;
import com.qfedu.empproject.utils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;


public class EmpDaoImpl implements EmpDao {
    private QueryRunner queryRunner = new QueryRunner();

    @Override
    public int insert(Emp emp) {
        int insert = 0;
        try {
            DbUtils.begin();
            String sql = "insert into emp(name,salary,age) values(?,?,?)";
            insert = queryRunner.update(DbUtils.getConnection(),
                    sql, emp.getName(), emp.getSalary(), emp.getAge());
            DbUtils.commit();
        } catch (SQLException e) {
            DbUtils.rollback();
            e.printStackTrace();
        }
        return insert;
    }

    @Override
    public int update(Emp emp) {
        int update = 0;
        try {
            DbUtils.begin();
            String sql = "update emp set name=?,salary=?,age=? where id=?";
            update = queryRunner.update(DbUtils.getConnection(),
                    sql, emp.getName(), emp.getSalary(), emp.getAge(), emp.getId());
            DbUtils.commit();
        } catch (SQLException e) {
            DbUtils.rollback();
            e.printStackTrace();
        }
        return update;
    }

    @Override
    public int updateDynamic(Emp emp) {
        int update = 0;
        try {
            // 动态更新的实现步骤 1、动态拼接SQL(占位符，逗号等) 2、动态传参
            String name = emp.getName();
            Integer age = emp.getAge();
            Double salary = emp.getSalary();

            StringBuilder sb = new StringBuilder("update emp set ");
            List<Object> params = new ArrayList<>();

            if (Objects.nonNull(name) && !"".equals(name)) {
                sb.append(" name=?,");
                params.add(name);
            }
            if (Objects.nonNull(salary)) {
                sb.append(" salary=?,");
                params.add(salary);
            }
            if (Objects.nonNull(age)) {
                sb.append(" age=?,");
                params.add(age);
            }
            String sql = sb.toString();
            if (sql.endsWith(",")) {
                sql = sql.substring(0, sql.length() - 1);
            }
            sql = sql.concat(" where id=?");
            params.add(emp.getId());
            // SQL拼接和动态传参完毕
            DbUtils.begin();
            // List转成数组传递，不能直接声明成数组否则无法指定下标赋值
            update = queryRunner.update(DbUtils.getConnection(), sql, params.toArray());
            DbUtils.commit();
        }catch (SQLException e){
            DbUtils.rollback();
            e.printStackTrace();
        }
        return update;
    }

    @Override
    public int delete(Integer id) {
        int delete = 0;
        try {
            DbUtils.begin();
            String sql = "delete from emp where id=?";
            delete = queryRunner.update(DbUtils.getConnection(), sql, id);
            DbUtils.commit();
        } catch (SQLException e) {
            DbUtils.rollback();
            e.printStackTrace();
        }
        return delete;
    }

    @Override
    public Emp selectOne(Integer id) {
        Emp emp = null;
        try {
            DbUtils.begin();
            String sql = "select * from emp where id=?";
            emp = queryRunner.query(DbUtils.getConnection(), sql,
                    new BeanHandler<>(Emp.class), id);
            DbUtils.commit();
        } catch (SQLException e) {
            DbUtils.rollback();
            e.printStackTrace();
        }
        return emp;
    }

    @Override
    public List<Emp> selectByPage(Integer from, Integer size) {
        List<Emp> emp = null;
        try {
            DbUtils.begin();
            String sql = "select * from emp limit ?,?";
            emp = queryRunner.query(DbUtils.getConnection(), sql,
                    new BeanListHandler<>(Emp.class), from, size);
            DbUtils.commit();
        } catch (SQLException e) {

            DbUtils.rollback();
            e.printStackTrace();
        }
        return emp;
    }

    @Override
    public long getCount() {
        long count = 0;
        try {
            DbUtils.begin();
            String sql = "select count(*) from emp";
            count = queryRunner.query(DbUtils.getConnection(), sql,
                    new ScalarHandler<>());
            DbUtils.commit();
        } catch (SQLException e) {
            DbUtils.rollback();
            e.printStackTrace();
        }
        return count;
    }
}
